package org.compiere.process;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import javax.swing.JFileChooser;
import javax.swing.JList;

import jxl.format.Border;

//import jxl.Range;
//import jxl.Workbook;
//import jxl.format.Alignment;
//import jxl.format.Border;
//import jxl.format.BorderLineStyle;
//import jxl.format.CellFormat;
//import jxl.format.Colour;
//import jxl.format.Format;
//import jxl.format.VerticalAlignment;
//import jxl.write.Label;
//import jxl.write.Number;
//import jxl.write.NumberFormats;
//import jxl.write.WritableCellFormat;
//import jxl.write.WritableFont;
//import jxl.write.WritableSheet;
//import jxl.write.WritableWorkbook;




import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBCSACRInputFormAppend1;
import org.compiere.model.MBCSACRInputFormAppend2Ext;
import org.compiere.model.MBCSACRInputFormAppend3_1;
import org.compiere.model.MBCSACRInputFormAppend3_1Ext;
import org.compiere.model.MBCSACRInputFormAppend3_2Ext;
import org.compiere.model.MBCSACRInputFormAppend3_3Ext;
import org.compiere.model.MBCSACRInputFormAppend4;
import org.compiere.model.MBCSACRInputFormCompany;
import org.compiere.model.MBCSACRInputFormIntCompany;
import org.compiere.model.MBCSACRInputFormPeriod;
import org.compiere.model.MBCSACRInputFormVGoo2;
import org.compiere.model.MBCSACRInputFormVGoo3;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.MPeriod;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

import org.utils.DataUtils;

public class BCS_ACReport extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private BigDecimal PeriodFrom = null;
	private MPeriod mPeriodFrom = null;
	
	private BigDecimal PeriodTo = null;
	private MPeriod mPeriodTo = null;
	
	private BigDecimal Year = null;
	private int iYear = 0;
	private boolean isConsolidation = false;
	private BigDecimal C_BPartner = null;
	private String Application = "";

	private static CLogger s_log = CLogger.getCLogger(BCS_ACReport.class);
	/** */
	private String path = "";

	private int AD_Org_ID = -1;
	private int AD_Client_ID = -1;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("PeriodFrom")) {
				PeriodFrom = (BigDecimal) para[i].getParameter();
				mPeriodFrom = new MPeriod(getCtx(), PeriodFrom.intValue(), null);
			}else if (name.equals("PeriodTo")) {
				PeriodTo = (BigDecimal) para[i].getParameter();
				mPeriodTo = new MPeriod(getCtx(), PeriodTo.intValue(), null);
			}else if (name.equals("C_Year_ID")) {
				Year = (BigDecimal) para[i].getParameter();
				iYear = Integer.parseInt(para[i].getInfo());
			} else if (name.equals("isConsolidation")) {
				isConsolidation = ((String) para[i].getParameter()).equals("Y");
			} else if (name.equals("C_BPartner_ID")) {
				C_BPartner = (BigDecimal) para[i].getParameter();
			}
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		// AD_Client_ID = getAD_Client_ID();
		// AD_Org_ID = getAD_Org_ID();

		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {
		if (isConsolidation)
			return isConsolidation();
		else
			return isNotConsolidation();
	}

	protected String isNotConsolidation() throws Exception {
		MBCSACRInputFormPeriod period = getMBCSACRInputFormPeriod(PeriodFrom, PeriodTo, Year);
		List<MBCSACRInputFormCompany> companies = getMBCSACRInputFormCompany(period
				.getBCS_ACRInputForm_Period_ID(), C_BPartner);
	
		if (period == null || companies.size() == 0)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют.");
		
			MBCSACRInputFormAppend1 append1 = getMBCSACRInputFormAppend1(companies.get(0).get_ID());		
			List<MBCSACRInputFormAppend2Ext> append2List = getMBCSACRInputFormAppend2Ext(companies);
			List<MBCSACRInputFormAppend3_1Ext> append3_1List = getMBCSACRInputFormAppend3_1Ext(companies);
			List<MBCSACRInputFormAppend3_2Ext> append3_2List = getMBCSACRInputFormAppend3_2Ext(companies);
			List<MBCSACRInputFormAppend3_3Ext> append3_3List = getMBCSACRInputFormAppend3_3Ext(companies);
			List<MBCSACRInputFormAppend4> append4 = getMBCSACRInputFormAppend4(companies.get(0).get_ID());
			List<MBCSACRInputFormIntCompany> interCompanyList = getMBCSACRInputFormInterCompany(companies.get(0).get_ID());
			
			String file = "AuditCommittee.xls";
			MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
			if (entry == null)
				throw new AdempiereException(Msg.translate(m_ctx,
						"NotFoundTemplate"));

			HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
			HSSFSheet sheet = wb.getSheetAt(0);
			Row R = null;
			Cell C = null;
			CellStyle style = null;

			HSSFFont cf = wb.createFont();
			cf.setFontHeightInPoints((short) 10);
			cf.setItalic(true);
			cf.setFontName("Times New Roman");

			HSSFCellStyle remark = wb.createCellStyle();
			remark.setFont(cf);

			
			HSSFFont boldFont = wb.createFont();
			boldFont.setFontHeightInPoints((short) 10);
			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			boldFont.setFontName("Times New Roman");

			HSSFCellStyle bsv = wb.createCellStyle();
			bsv.setFont(boldFont);
			bsv.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			bsv.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			bsv.setBorderTop(HSSFCellStyle.BORDER_THIN);
			bsv.setBorderRight(HSSFCellStyle.BORDER_THIN);
			bsv.setWrapText(true);
			bsv.setAlignment(HSSFCellStyle.ALIGN_RIGHT);			
			
			HSSFCellStyle boldStyle = wb.createCellStyle();
			boldStyle.setFont(boldFont);
			boldStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			boldStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			boldStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			boldStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			boldStyle.setWrapText(true);
			boldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//			boldStyle.setShrinkToFit(true);

			HSSFCellStyle boldStyleWithoutBorder = wb.createCellStyle();
			boldStyleWithoutBorder.setFont(boldFont);

			
			HSSFCellStyle boldStyle2 = wb.createCellStyle();
			boldStyle2.setFont(boldFont);
			boldStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			boldStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			boldStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
			boldStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
			boldStyle2.setWrapText(true);
			boldStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			boldStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			
			HSSFFont Font = wb.createFont();
			boldFont.setFontHeightInPoints((short) 10);
//			boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			boldFont.setFontName("Times New Roman");

			HSSFCellStyle Style = wb.createCellStyle();
			Style.setFont(Font);
			Style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			Style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			Style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			Style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			Style.setWrapText(true);

			HSSFCellStyle StyleBottomLine = wb.createCellStyle();
			StyleBottomLine.setBorderBottom(HSSFCellStyle.BORDER_THIN);

			HSSFCellStyle StyleHeader = wb.createCellStyle();
			StyleHeader.setFont(boldFont);
//			StyleHeader.setFillBackgroundColor(IndexedColors.PALE_BLUE.getIndex());
//			StyleHeader.setFillPattern(CellStyle.BIG_SPOTS);
			StyleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			StyleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			StyleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);
			StyleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);
			StyleHeader.setWrapText(true);
			StyleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			StyleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);			
			
			// ---------------------------------------------------------------
			// appendix1
			R = sheet.getRow(9);
			C = R.getCell(2);
			
			
			String s = append1.getBCS_FormOfIncorporation();
			if(s.equals("JSC"))
				C.setCellValue("Акционерное общество");
			else if(s.equals("LLC"))
				C.setCellValue("Товарищество с ограниченной ответсвенностью");
	
			R = sheet.getRow(10);
			C = R.getCell(2);
			C.setCellValue(append1.getFullAddress());
	
			R = sheet.getRow(11);
			C = R.getCell(2);
			C.setCellValue(append1.getPhone());
	
			R = sheet.getRow(12);
			C = R.getCell(2);
			C.setCellValue("" + isNull(append1.getEMail()) +"\n " +isNull(append1.getURL()));
	
			R = sheet.getRow(13);
			C = R.getCell(2);
			C.setCellValue(append1.getFI_BIN_IIN());
	
			R = sheet.getRow(14);
			C = R.getCell(2);
			C.setCellValue(append1.getFI_TIN());
	
			R = sheet.getRow(15);
			C = R.getCell(2);
			C.setCellValue(append1.getBCS_GoverningAgencyName());
	
			R = sheet.getRow(17);
			C = R.getCell(2);
			C.setCellValue(append1.getBCS_HistoryOfCreation());
	
			R = sheet.getRow(18);
			C = R.getCell(2);
			
			String d = "";
			if(append1.getDate1() != null){
				Calendar c = Calendar.getInstance();
				c.setTime(append1.getDate1());
				d = " от " + c.get(Calendar.DAY_OF_MONTH) + " " +org.utils.DataUtils.getMonthNameRU(new Date(append1.getDate1().getTime())).toLowerCase() + " " + c.get(Calendar.YEAR) + "г.";;
			}
			
			s = "";
			if(!isNull(append1.getNumber2()).equals(""))
				s = "№" + isNull(append1.getNumber2()) + d;
			
			C.setCellValue(s);
	
			R = sheet.getRow(21);
			C = R.getCell(2);
			C.setCellValue(append1.getBCS_CreationPurposes());
	
			R = sheet.getRow(22);
			C = R.getCell(2);
			C.setCellValue(append1.getBCS_Activities());
	
			R = sheet.getRow(23);
			C = R.getCell(2);
			C.setCellValue(append1.getBCS_ReorganizationInfo());

			
			
			// appendix2
			sheet = wb.getSheetAt(1);
			int row = 9;
			int number = 1;
			R = sheet.getRow(7);
			C = R.createCell(2);
			C.setCellStyle(StyleHeader);
			
			MBPartner p = new MBPartner(getCtx(), companies.get(0).getC_BPartner_ID(), null);
			String name = "";
			if(p != null)
				name = p.getName();
			
			C.setCellValue(name);
			
			R = sheet.getRow(8);
			C = R.createCell(2);
			C.setCellStyle(StyleHeader);
//			String v = "Прошлый период ("+ 
//					DataUtils.getMonthNameRUIP(mPeriodFrom.getEndDate()).toLowerCase() +
//					" - " +
//					DataUtils.getMonthNameRUIP(mPeriodTo.getEndDate()).toLowerCase() +
//					" " +	(iYear-1) +"г.)";
			String v = "Прошлый период";

			C.setCellValue(v);

			setRowHeight(sheet, R, v, 13);
//			int len = v.length();
//			int height = 0; 
//			if(len > 0){
//			 int diff = len / 13;
//			 if(diff > 0){
////				 height = (diff + 1) * 300;
////				 R.setHeight((short)height);
//				 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//			 }
//			}
			
			R = sheet.getRow(8);
			C = R.createCell(3);
			C.setCellStyle(StyleHeader);
//			v = "Прошлый период  - оценка ("+ 
//					DataUtils.getMonthNameRUIP(mPeriodFrom.getEndDate()).toLowerCase() +
//					" - " +
//					DataUtils.getMonthNameRUIP(mPeriodTo.getEndDate()).toLowerCase() +
//					" " +	(iYear) +"г.)";
			v = "Отчетный период";
			C.setCellValue(v);

			setRowHeight(sheet, R, v, 13);
//			len = v.length();
//			height = 0; 
//			if(len > 0){
//			 int diff = len / 13;
//			 if(diff > 0){
////				 height = (diff + 1) * 300;
////				 R.setHeight((short)height);
//				 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//			 }
//			}
			
			
			for (MBCSACRInputFormAppend2Ext line : append2List) {

				R = sheet.createRow(row);
				R = sheet.getRow(row);

				if (R != null) {
//					dict d = getDict(line.getBCS_ACRPositionsDict_ID());
					
					C = R.createCell(0);
					if(line.isGroup()) {
						C.setCellStyle(boldStyle); //
						C.setCellValue(number++);
					}else C.setCellStyle(Style);
					
					
					C = R.createCell(1);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					setRowHeight(sheet, R, line.getDictName(), 60);
//					len = line.getDictName().length();
//					height = 0; 
//					if(len > 0){
//					 int diff = len / 60;
//					 if(diff > 0){
//						 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//					 }
//					}
					
					

					
					C.setCellValue(line.getDictName());
					
					C = R.createCell(2);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					C.setCellValue(line.getValue1());
					
					C = R.createCell(3);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);

					if(line.getBCS_ACRPositionsDict_ID() == 1000002)
						C.setCellFormula("" + line.getValue2() + "-\'4\'!C7");
					if(line.getBCS_ACRPositionsDict_ID() == 1000014)
						C.setCellFormula("" + line.getValue2() + "-\'4\'!C8");
					if(line.getBCS_ACRPositionsDict_ID() == 1000016)
						C.setCellFormula("" + line.getValue2() + "-\'4\'!C9");
					if(line.getBCS_ACRPositionsDict_ID() == 1000017)
						C.setCellFormula("" + line.getValue2() + "-\'4\'!C10");
					if(line.getBCS_ACRPositionsDict_ID() == 1000025)
						C.setCellFormula("" + line.getValue2() + "-\'4\'!C11");					
					else
						C.setCellValue(line.getValue2());
				}
				row++;
			}
			
			row++;
			row++;
			
			R = sheet.createRow(row);
			sheet.addMergedRegion(new Region((short)row, (short)1, (short)row, (short)1));
			C = R.createCell(1);
			C.setCellStyle(boldStyleWithoutBorder);
			C.setCellValue("Заместитель Председателя Правления  Курганбекова З.Е.");
			
			C = R.createCell(2);
			C.setCellStyle(StyleBottomLine);
			C = R.createCell(3);
			C.setCellStyle(StyleBottomLine);

			//appendix3*------------------------------------
			sheet = wb.getSheetAt(2);
			row = 9;
			number = 1;

			R = sheet.getRow(7);
			C = R.createCell(3);
			C.setCellStyle(StyleHeader);
			
			if(p != null)
				name = p.getName();
			
			C.setCellValue(name);
			
			R = sheet.getRow(8);
			C = R.createCell(3);
			C.setCellStyle(StyleHeader);
//			C.setCellValue("на 1 января "+ (iYear) +" г. (оценка)");
			Date d1 = mPeriodTo.getEndDate();
			Calendar c = new GregorianCalendar();
			c.setTime(d1);
			c.add(Calendar.DAY_OF_MONTH, 1);
			
//			String month = "на 1 " + DataUtils.getMonthNameRU(c.getTime()).toLowerCase() + " " + c.get(Calendar.YEAR) + " г.";
			
			C.setCellValue("на конец отчетного периода");	//"на 1 января "+iYear+" года");
			
			
			R = sheet.getRow(8);
			C = R.createCell(4);
			C.setCellStyle(StyleHeader);
			
//			C.setCellValue("на 1 января "+ (iYear-1) +"г.");
//			month = "на 1 " + DataUtils.getMonthNameRU(c.getTime()).toLowerCase() + " " + (c.get(Calendar.YEAR)-1) + " г.";
			
			C.setCellValue("на начало отчетного периода");	//"на 1 января "+iYear+" года");
			
			
			for (MBCSACRInputFormAppend3_1Ext line : append3_1List) {

				R = sheet.createRow(row);
				R = sheet.getRow(row);

				// C = R.createCell(0);
				// C = R.createCell(1);
				if (R != null) {
//					dict d = getDict(line.getBCS_ACRPositionsDict_ID());
					
					C = R.createCell(0);
//					if(line.isGroup()) C.setCellStyle(boldStyle); else 
						C.setCellStyle(bsv);
					C.setCellValue(line.getRow());
					
					C = R.createCell(1);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					setRowHeight(sheet, R, line.getDictName(), 60);
//					len = line.getDictName().length();
//					height = 0; 
//					if(len > 0){
//					 int diff = len / 60;
//					 if(diff > 0){
////						 height = (diff + 1) * 300;
////						 R.setHeight((short)height);
//						 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//					 }
//					}

					
					C.setCellValue(line.getDictName());

					C = R.createCell(2);
					C.setCellStyle(Style);
					C.setCellValue(line.getUom());				
					
					C = R.createCell(3);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					C.setCellValue(line.getValue1());
					
					C = R.createCell(4);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					
					if(line.getBCS_ACRPositionsDict_ID() == 1000030)
						C.setCellFormula("" + line.getValue2() + "-\'5\'!C7");
					if(line.getBCS_ACRPositionsDict_ID() == 1000038)
						C.setCellFormula("" + line.getValue2() + "-\'5\'!C8");
					if(line.getBCS_ACRPositionsDict_ID() == 1000033)
						C.setCellFormula("" + line.getValue2() + "-\'5\'!C9");
					if(line.getBCS_ACRPositionsDict_ID() == 1000039)
						C.setCellFormula("" + line.getValue2() + "-\'5\'!C10");
					else
						C.setCellValue(line.getValue2());

				}
				row++;
//				number++;
			}
			// sheet = wb.getSheetAt(2);
			// row = 7;

			// appendix3_2
			R = sheet.createRow(row);
			R = sheet.getRow(row);
//			sheet.addMergedRegion(new Region((short)row, (short)0, (short)row, (short)4));
			C = R.createCell(0);
			C.setCellStyle(bsv);
			C = R.createCell(1);
			C.setCellStyle(boldStyle2);
			C.setCellValue("Показатели отчета о прибылях и убытках");
			C = R.createCell(2);
			C.setCellStyle(boldStyle2);
			C.setCellValue("Ед. измерения");
			
			int len2 = "Ед. измерения".length();
			int height2 = 0; 
			if(len2 > 0){
			 int diff = len2 / 7;
			 if(diff > 0){
				 height2 *= 300;
				 R.setHeight((short)height2);
			 }
			}

			
			C = R.createCell(3);
			C.setCellStyle(boldStyle2);
			C.setCellValue("За отчетный период");
			C = R.createCell(4);
			C.setCellStyle(boldStyle2);
			C.setCellValue("За предыдущий период");
			
			
			row++;

			for (MBCSACRInputFormAppend3_2Ext line : append3_2List) {
//				int len, height;
				R = sheet.createRow(row);
				R = sheet.getRow(row);

				if (R != null) {
//					dict d = getDict(line.getBCS_ACRPositionsDict_ID());

					if(line.getRow() != null && line.getRow().equals("8")){
//						sheet.addMergedRegion(new Region((short)row, (short)0, (short)row, (short)4));
						C = R.createCell(0);
						C.setCellStyle(bsv);
						C = R.createCell(1);
						C.setCellStyle(boldStyle2);
						C.setCellValue("Прочие уточняющие данные");
						C = R.createCell(2);
						C.setCellStyle(boldStyle2);
						C.setCellValue("Ед. измерения");
						setRowHeight(sheet, R, "Ед. измерения", 7);
//						len = "Ед. измерения".length();
//						height = 0; 
//						if(len > 0){
//						 int diff = len / 7;
//						 if(diff > 0){
////							 height = (diff + 1) * 30;
////							 
////							 R.setHeight((short)height);
//							 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));
//						 }
//						}

						
						
						C = R.createCell(3);
						C.setCellStyle(boldStyle2);
						C.setCellValue("За отчетный период");
						C = R.createCell(4);
						C.setCellStyle(boldStyle2);
						C.setCellValue("За предыдущий период");

						row++;
						R = sheet.createRow(row);
						R = sheet.getRow(row);
					}
					
					C = R.createCell(0);
//					if(line.isGroup()) C.setCellStyle(boldStyle); else 
						C.setCellStyle(bsv);
					C.setCellValue(line.getRow());
					
					C = R.createCell(1);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					setRowHeight(sheet, R, line.getDictName(), 60);
//					len = line.getDictName().length();
//					height = 0; 
//					if(len > 0){
//					 int diff = len / 60;
//					 if(diff > 0){
////						 height = (diff + 1) * 300;
////						 R.setHeight((short)height);
//						 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));
//					 }
//					}
					
					C.setCellValue(line.getDictName());

					C = R.createCell(2);
					C.setCellStyle(Style);
					C.setCellValue(line.getUom());
					
					C = R.createCell(3);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					if(line.getValue1() != 0)
						C.setCellValue(line.getValue1());
					else
						C.setCellValue(0);
					
					C = R.createCell(4);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					if(line.getValue2() != 0)
						C.setCellValue(line.getValue2());
					else
						C.setCellValue(0);
				}
				row++;
				number++;
			}

			
			// appendix3_3
			R = sheet.createRow(row);
			R = sheet.getRow(row);
			sheet.addMergedRegion(new Region((short)row, (short)0, (short)row, (short)4));
			C = R.createCell(0);
			C.setCellStyle(boldStyle2);
			C.setCellValue("Сведения по предыдущему контролю/аудиту");
			C = R.createCell(4);
			C.setCellStyle(boldStyle2);
			
			
			row++;

			for (MBCSACRInputFormAppend3_3Ext line : append3_3List) {

				R = sheet.createRow(row);
				R = sheet.getRow(row);

				if (R != null) {
//					dict d = getDict(line.getBCS_ACRPositionsDict_ID());
					
					C = R.createCell(0);
//					if(line.isGroup()) C.setCellStyle(boldStyle); else 
						C.setCellStyle(bsv);
					C.setCellValue(line.getRow());
					
					sheet.addMergedRegion(new Region((short)row, (short)1, (short)row, (short)2));
					C = R.createCell(1);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					setRowHeight(sheet, R, line.getDictName(), 60);
//					len = line.getDictName().length();
//					height = 0; 
//					if(len > 0){
//					 int diff = len / 60;
//					 if(diff > 0){
////						 height = (diff + 1) * 300;
////						 R.setHeight((short)height);
//						 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//					 }
//					}
					
					C.setCellValue(line.getDictName());

					C = R.createCell(2);
					C.setCellStyle(Style);
//					C.setCellValue("");
					sheet.addMergedRegion(new Region((short)row, (short)3, (short)row, (short)4));					
					C = R.createCell(3);
					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
					if(line.getValue1() != null && !line.getValue1().equals(""))
						C.setCellValue(line.getValue1());
					
					C = R.createCell(4);
					C.setCellStyle(Style);
//					if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
//					if(line.getValue2() > 0)
//						C.setCellValue(line.getValue2());
					C = R.createCell(4);
					C.setCellStyle(Style);
				}
				row++;
				number++;
			}
			
			
			row++;
//			row++;

			R = sheet.createRow(row);
			C = R.createCell(0);
			C = R.createCell(1);
			C.setCellStyle(remark);
			C.setCellValue("Примечание:");
			row++;
			R = sheet.createRow(row);
			C = R.createCell(0);
			C = R.createCell(1);
			C.setCellStyle(remark);
			C.setCellValue("1) приложение предоставляется как в бумажном, так и в электронном виде (Excel)");
			row++;
			R = sheet.createRow(row);
			C = R.createCell(0);
			C = R.createCell(1);
			C.setCellStyle(remark);
			C.setCellValue("2) все поля являются обязательными для заполнения");
			
			
			
			row++;
			row++;
			
			R = sheet.createRow(row);
			sheet.addMergedRegion(new Region((short)row, (short)1, (short)row, (short)2));
			C = R.createCell(1);
			C.setCellStyle(boldStyleWithoutBorder);
			C.setCellValue("Заместитель Председателя Правления  Курганбекова З.Е.");
			
			C = R.createCell(3);
			C.setCellStyle(StyleBottomLine);
			C = R.createCell(4);
			C.setCellStyle(StyleBottomLine);
			
			
			// appendix4-------------------------------------------------
			sheet = wb.getSheetAt(5);
			row = 6;
			number = 1;
			for (MBCSACRInputFormAppend4 line : append4) {
	
				R = sheet.createRow(row);
				R = sheet.getRow(row);
				if (R != null) {
	
					C = R.createCell(0);
					C.setCellStyle(boldStyle);
					C.setCellValue(number);
	
					C = R.createCell(1);
					C.setCellStyle(Style);
					C.setCellValue(line.getBCS_AnnualFIStatementOvePeriod());
	
					C = R.createCell(2);
					C.setCellStyle(Style);
					
					
					d = "";
					if(line.getBCS_ApprovalDate() != null){
						c = Calendar.getInstance();
						c.setTime(line.getBCS_ApprovalDate());
						d = " от " + c.get(Calendar.DAY_OF_MONTH) + " " +org.utils.DataUtils.getMonthNameRU(new Date(line.getBCS_ApprovalDate().getTime())).toLowerCase() + " " + c.get(Calendar.YEAR) + "г.";;
					}
					
					if(!isNull(append1.getNumber2()).equals(""))
						s = "№" + isNull(append1.getNumber2()) + d;
					
					setRowHeight(sheet, R, s, 15);
//					len = s.length();
//					height = 0; 
//					if(len > 0){
//					 int diff = len / 15;
//					 if(diff > 0){
////						 height = (diff + 1) * 300;
////						 R.setHeight((short)height);
//						 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//					 }
//					}

					
					C.setCellValue(s);

//					SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
//					C.setCellValue(line.getBCS_DocNo_OrderProtocol() + " от "
//							+ format.format(line.getBCS_ApprovalDate()));
	
					C = R.createCell(3);
					C.setCellStyle(Style);
					C.setCellValue(line.getBCS_TotalRevenues());
	
					C = R.createCell(4);
					C.setCellStyle(Style);
					C.setCellValue(line.getBCS_TotalExpenses());
	
					C = R.createCell(5);
					C.setCellStyle(Style);
					C.setCellValue(line.getBCS_NetProfitLoss());
	
					C = R.createCell(6);
					C.setCellStyle(Style);
					C.setCellValue(line.getBCS_NumberAverage());
	
					C = R.createCell(7);
					C.setCellStyle(Style);
					C.setCellValue(line.getBCS_Dividends_PartOfNetProfit());
	
				}
				row++;
				number++;
			}
	
			
			row++;
			row++;
			
			R = sheet.createRow(row);
			sheet.addMergedRegion(new Region((short)row, (short)0, (short)row, (short)3));
			C = R.createCell(0);
			C.setCellStyle(boldStyleWithoutBorder);
			C.setCellValue("Заместитель Председателя Правления  Курганбекова З.Е.");
			
			C = R.createCell(4);
			C.setCellStyle(StyleBottomLine);
			C = R.createCell(5);
			C.setCellStyle(StyleBottomLine);
			C = R.createCell(6);
			C.setCellStyle(StyleBottomLine);
			
			//---------------vgoo=============================================
			// vgoo2
			sheet = wb.getSheetAt(3);
			row = 5;
			int col = 3;
			number = 1;
			for (MBCSACRInputFormIntCompany line : interCompanyList) {

				R = sheet.getRow(row);
				if(R == null)
					R = sheet.createRow(row);
				
				C = R.createCell(col);
				C.setCellStyle(boldStyle);
				
				MBPartner bp = new MBPartner(Env.getCtx(), line.getC_BPartner_ID(), null);
				
				C.setCellValue(bp.getName());

				List<MBCSACRInputFormVGoo2> vgoo2 = getMBCSACRInputFormVGoo2(line.get_ID());
				int i=0;
				for(MBCSACRInputFormVGoo2 vg: vgoo2){
					i++;
					R = sheet.getRow(row+i);
					if(R == null)
						R = sheet.createRow(row+i);
					
					C = R.createCell(col);
					C.setCellStyle(Style);
					C.setCellValue(vg.getValue1());

				}
				
				

				col++;
			}
	

			
			// vgoo3
			sheet = wb.getSheetAt(4);
			row = 5;
			col = 3;
			number = 1;
			for (MBCSACRInputFormIntCompany line : interCompanyList) {

				R = sheet.getRow(row);
				if(R == null)
					R = sheet.createRow(row);
				
				C = R.createCell(col);
				C.setCellStyle(boldStyle);
				
				MBPartner bp = new MBPartner(Env.getCtx(), line.getC_BPartner_ID(), null);
				
				C.setCellValue(bp.getName());

				List<MBCSACRInputFormVGoo3> vgoo3 = getMBCSACRInputFormVGoo3(line.get_ID());
				int i=0;
				for(MBCSACRInputFormVGoo3 vg: vgoo3){
					i++;
					R = sheet.getRow(row+i);
					if(R == null)
						R = sheet.createRow(row+i);
					
					C = R.createCell(col);
					C.setCellStyle(Style);
					C.setCellValue(vg.getValue1());

				}

				col++;
			}
			
			
//			row++;
//			row++;
//			
//			R = sheet.createRow(row);
//			sheet.addMergedRegion(new Region((short)row, (short)0, (short)row, (short)3));
//			C = R.createCell(0);
//			C.setCellStyle(boldStyleWithoutBorder);
//			C.setCellValue("Заместитель Председателя Правления  Курганбекова З.Е.");
//			
//			C = R.createCell(4);
//			C.setCellStyle(StyleBottomLine);
//			C = R.createCell(5);
//			C.setCellStyle(StyleBottomLine);
//			C = R.createCell(6);
//			C.setCellStyle(StyleBottomLine);
			



			
			FileOutputStream fileOut = new FileOutputStream(file);
			wb.write(fileOut);
			fileOut.close();
			// Open the file with the application associated
			if(!Env.isWindows())
				Util.launchFile(new File(file));
			else{
				try {
					String command = "excel \"" + file + "\"";
					Runtime.getRuntime().exec("cmd /c start " + command);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}

			return Msg.translate(m_ctx, "Success");

	}

	private List<MBCSACRInputFormIntCompany> getMBCSACRInputFormInterCompany(int ID) {
		List<MBCSACRInputFormIntCompany> list = new ArrayList<MBCSACRInputFormIntCompany>();
		
		PreparedStatement pstmt = null; 
		ResultSet rs = null;
		String sql = "";

		sql = "select ic.bcs_acrinputform_intcompany_id	\n" +
				"from adempiere.bcs_acrinputform_intcompany ic	\n" +  
				"where ic.BCS_ACRInputForm_Company_ID=" + ID;

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				MBCSACRInputFormIntCompany line = new MBCSACRInputFormIntCompany(Env.getCtx(), rs.getInt(1),
						null);
				list.add(line);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		
		return list;
	}

	private List<MBCSACRInputFormIntCompany> getMBCSACRInputFormInterCompanyFromCompanies(List<MBCSACRInputFormCompany> companies) {
		List<MBCSACRInputFormIntCompany> list = new ArrayList<MBCSACRInputFormIntCompany>();
		
		PreparedStatement pstmt = null; 
		ResultSet rs = null;
		String sql = "";

//		sql = "select ic.bcs_acrinputform_intcompany_id	\n" +
//				"from adempiere.bcs_acrinputform_intcompany ic	\n" +  
//				"where ic.BCS_ACRInputForm_Company_ID=" + ID;

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				MBCSACRInputFormIntCompany line = new MBCSACRInputFormIntCompany(Env.getCtx(), rs.getInt(1),
						null);
				list.add(line);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		
		return list;
	}

	
	protected String isConsolidation() throws Exception {

		MBCSACRInputFormPeriod period = getMBCSACRInputFormPeriod(PeriodFrom,
				PeriodTo, Year);
		
		if (period == null)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют.");

		List<MBCSACRInputFormCompany> companies = getMBCSACRInputFormCompany(period
				.getBCS_ACRInputForm_Period_ID(), null);

//		MBCSACRInputFormAppend1 ListOfAppend1 = getMBCSACRInputFormAppend1(companies
		List<MBCSACRInputFormAppend2Ext> ListOfAppend2Ext = getMBCSACRInputFormAppend2Ext(companies);
		List<MBCSACRInputFormAppend3_1Ext> ListOfAppend3_1Ext = getMBCSACRInputFormAppend3_1Ext(companies);
		List<MBCSACRInputFormAppend3_2Ext> ListOfAppend3_2Ext = getMBCSACRInputFormAppend3_2Ext(companies);
		List<MBCSACRInputFormAppend3_3Ext> ListOfAppend3_3Ext = getMBCSACRInputFormAppend3_3Ext(companies);
		List<MBCSACRInputFormIntCompany> interCompanyList = getMBCSACRInputFormInterCompany(companies.get(0).get_ID());
//		List<MBCSACRInputFormAppend4> ListOfappend4 = getMBCSACRInputFormAppend4(companies);

		String file = "AuditCommitteeConsolidation.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		wb.removeSheetAt(0);
//		wb.removeSheetAt(2);
//		wb.removeSheetAt(2);
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
//		Column column = null;
		CellStyle style = null;

		HSSFFont cf = wb.createFont();
		cf.setFontHeightInPoints((short) 10);
		cf.setItalic(true);
		cf.setFontName("Times New Roman");

		HSSFCellStyle remark = wb.createCellStyle();
		remark.setFont(cf);		
		
		HSSFFont boldFont = wb.createFont();
		boldFont.setFontHeightInPoints((short) 10);
		boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		boldFont.setFontName("Times New Roman");

		HSSFCellStyle boldStyle = wb.createCellStyle();
		boldStyle.setFont(boldFont);
		boldStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		boldStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		boldStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		boldStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		boldStyle.setWrapText(true);
		
		HSSFCellStyle bsv = wb.createCellStyle();
		bsv.setFont(boldFont);
		bsv.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		bsv.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		bsv.setBorderTop(HSSFCellStyle.BORDER_THIN);
		bsv.setBorderRight(HSSFCellStyle.BORDER_THIN);
		bsv.setWrapText(true);
		bsv.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

		
		HSSFCellStyle boldStyleWithBorder = wb.createCellStyle();
		boldStyleWithBorder.setFont(boldFont);
		boldStyleWithBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		boldStyleWithBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		boldStyleWithBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);
		boldStyleWithBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);
		boldStyleWithBorder.setWrapText(true);
		boldStyleWithBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		boldStyleWithBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFCellStyle boldStyleWithoutBorder = wb.createCellStyle();
		boldStyleWithoutBorder.setFont(boldFont);
		
		
		HSSFFont Font = wb.createFont();
		boldFont.setFontHeightInPoints((short) 10);
//		boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		boldFont.setFontName("Times New Roman");

		HSSFCellStyle Style = wb.createCellStyle();
		Style.setFont(Font);
//		Style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		Style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		Style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		Style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		Style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		Style.setWrapText(true);

		HSSFCellStyle sv = wb.createCellStyle();
		sv.setFont(Font);
//		Style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		sv.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		sv.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		sv.setBorderTop(HSSFCellStyle.BORDER_THIN);
		sv.setBorderRight(HSSFCellStyle.BORDER_THIN);
		sv.setWrapText(true);
		sv.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		
		HSSFCellStyle StyleHeader = wb.createCellStyle();
		StyleHeader.setFont(boldFont);
//		StyleHeader.setFillBackgroundColor(IndexedColors.PALE_BLUE.getIndex());
//		StyleHeader.setFillPattern(CellStyle.BIG_SPOTS);
		StyleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		StyleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		StyleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);
		StyleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);
		StyleHeader.setWrapText(true);
		StyleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		StyleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		
		HSSFCellStyle StyleBottomLine = wb.createCellStyle();
		StyleBottomLine.setBorderBottom(HSSFCellStyle.BORDER_THIN);

		
		HSSFFont bf = wb.createFont();
		bf.setFontHeightInPoints((short) 12);
		bf.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		bf.setFontName("Times New Roman");
		
		HSSFCellStyle sh = wb.createCellStyle();
		sh.setFont(bf);
		sh.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		sh.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		HSSFCellStyle boldStyle2 = wb.createCellStyle();
		boldStyle2.setFont(boldFont);
		boldStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		boldStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		boldStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		boldStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		boldStyle2.setWrapText(true);
		boldStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		boldStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);		
		
//		// ---------------------------------------------------------------

		// appendix2
		sheet = wb.getSheetAt(0);
		
		int row = 9;
		int rowCount = 0;
		int number = 1;
		int column = 2;
		boolean first = true;
		
		R = sheet.getRow(5);
		C = R.createCell(1);
		C.setCellStyle(sh);
		C.setCellValue("консолидированная");

		
		for(MBCSACRInputFormCompany company : companies){
			
			row = 9;
			number = 1;
			
			MBPartner p = new MBPartner(getCtx(), company.getC_BPartner_ID(), null);
			String name = "";
			if(p != null)
				name = p.getName();
				
			R = sheet.getRow(7);
			if(R == null)
				R = sheet.createRow(7);
			
			sheet.addMergedRegion(new Region((short)7, (short)column, (short)7, (short)(column+1)));
			C = R.createCell(column);
			C.setCellValue(name);
			C.setCellStyle(StyleHeader);
			C = R.createCell(column+1);
			C.setCellStyle(StyleHeader);

			R = sheet.getRow(8);
			sheet.setColumnWidth(column, 4000);
			C = R.createCell(column);
			C.setCellStyle(StyleHeader);
			
			String months = "";
			String mx = "";
			//
//			Date d = mPeriodFrom.getStartDate();
//			months = DataUtils.getMonthNameRUIP(d);
//			months += " - ";
//			
//			d = mPeriodTo.getStartDate();
//			months += DataUtils.getMonthNameRUIP(d);
//
//			mx = "Прошлый период (" + (iYear-1)+" г.)";
//			String v = "Прошлый период ("+ 
//					DataUtils.getMonthNameRUIP(mPeriodFrom.getEndDate()).toLowerCase() +
//					" - " +
//					DataUtils.getMonthNameRUIP(mPeriodTo.getEndDate()).toLowerCase() +
//					" " +	(iYear-1) +"г.)";
			String v = "Прошлый период";
			C.setCellValue(v);
			setRowHeight(sheet, R, v, 13);
//			int len = v.length();
//			int height = 0; 
//			if(len > 0){
//			 int diff = len / 13;
//			 if(diff > 0){
////				 height = (diff + 1) * 300;
////				 R.setHeight((short)height);
//				 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//			 }
//			}			
//			C.setCellValue(mx);

			C = R.createCell(column+1);
			C.setCellStyle(StyleHeader);
			sheet.setColumnWidth(column+1, 4000);			

//			v = "Прошлый период ("+ 
//					DataUtils.getMonthNameRUIP(mPeriodFrom.getEndDate()).toLowerCase() +
//					" - " +
//					DataUtils.getMonthNameRUIP(mPeriodTo.getEndDate()).toLowerCase() +
//					" " +	(iYear) +"г.)";
			v = "Отчетный период";
			C.setCellValue(v);
			setRowHeight(sheet, R, v, 13);
//			len = v.length();
//			height = 0; 
//			if(len > 0){
//			 int diff = len / 13;
//			 if(diff > 0){
////				 height = (diff + 1) * 300;
////				 R.setHeight((short)height);
//				 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//			 }
//			}			
//			mx = "Отчетный период (" +(iYear)+" г.)";
//			C.setCellValue(mx);
			
//			C.setCellValue(mx);
			
			for (MBCSACRInputFormAppend2Ext line : ListOfAppend2Ext) {


				if(company.getBCS_ACRInputForm_Company_ID() == line.getBCS_ACRInputForm_Company_ID()){
					if(first){
						rowCount++;
						R = sheet.createRow(row);
	//					R = sheet.createRow(row);
	//					R = sheet.getRow(row);
	//		
						if (R != null) {
//							dict d = getDict(line.getBCS_ACRPositionsDict_ID());
							C = R.createCell(0);
							if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
							if(line.isGroup())
								C.setCellValue(number++);
//							else
//								C.setCellValue("-");
							
							C = R.createCell(1);
							if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
							setRowHeight(sheet, R, line.getDictName(), 64);
//							len = line.getDictName().length();
//							height = 0; 
//							if(len > 0){
//							 int diff = len / 64;
//							 if(diff > 0){
////								 height = (diff + 1) * 300;
////								 R.setHeight((short)height);
//								 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));								 
//							 }
//							}

							
							C.setCellValue(line.getDictName());

							C = R.createCell(2);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
							C.setCellValue(line.getValue1());
							
							C = R.createCell(3);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
							C.setCellValue(line.getValue2());
	
//						C = R.createCell(0);
//						C.setCellValue(10);
//						
//						
//						C = R.createCell(1);
//		//					if(d.isSummary()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
//						C.setCellValue(d.getName());
//		//					System.out.println(row + ":		" + d.getName());
						}
					}else{

							R = sheet.getRow(row);
								if (R != null) {
//									dict d = getDict(line.getBCS_ACRPositionsDict_ID());
							
								C = R.createCell(column);
								if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
								C.setCellValue(line.getValue1());
								
								C = R.createCell(column+1);
								if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
								C.setCellValue(line.getValue2());
							}
					}
					row++;
////					number++;
				}
			}
			column++;
			column++;
			first = false;
		}

		//summary-------------------------
		
		R = sheet.getRow(7);
		C = R.createCell(column+1);
		C.setCellStyle(StyleHeader);
		
		sheet.addMergedRegion(new Region((short)7, (short)column, (short)7, (short)(column+1)));
		C = R.createCell(column);
		C.setCellStyle(StyleHeader);
		C.setCellValue("Консолидированный");
		
		R = sheet.getRow(8);
		sheet.setColumnWidth(column, 4000);
		C = R.createCell(column);
		C.setCellStyle(StyleHeader);

//		Date d = mPeriodFrom.getStartDate();
//		String months = DataUtils.getMonthNameRUIP(d);
//		months += " - ";
//		
//		d = mPeriodTo.getStartDate();
//		months += DataUtils.getMonthNameRUIP(d);
//
//		String mx = "Прошлый период (" + (iYear-1)+" г.)";
//		String v = "Прошлый период ("+ 
//				DataUtils.getMonthNameRUIP(new Date(PeriodFrom.longValue())).toLowerCase() +
//				" - " +
//				DataUtils.getMonthNameRUIP(new Date(PeriodTo.longValue())).toLowerCase() +
//				" " +	(iYear-1) +"г.)";
		String v = "Прошлый период";
		C.setCellValue(v);

		setRowHeight(sheet, R, v, 13);
//		int len = v.length();
//		int height = 0; 
//		if(len > 0){
//		 int diff = len / 13;
//		 if(diff > 0){
////			 height = (diff + 1) * 300;
////			 R.setHeight((short)height);
//			 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//		 }
//		}
		C.setCellValue(v);//"Прошлый период ("+(iYear-1)+" г.)");

		C = R.createCell(column+1);
		C.setCellStyle(StyleHeader);
		sheet.setColumnWidth(column+1, 4000);			

		C = R.createCell(column+1);
		C.setCellStyle(StyleHeader);
		sheet.setColumnWidth(column+1, 4000);
//		mx = "Отчетный период (" +(iYear)+" г.)";
//		
//		C.setCellValue(mx);//"Отчетный период  ("+iYear+" г.)");C.setCellValue("на конец отчетного периода");
//		v = "Прошлый период ("+ 
//				DataUtils.getMonthNameRUIP(mPeriodFrom.getEndDate()).toLowerCase() +
//				" - " +
//				DataUtils.getMonthNameRUIP(mPeriodTo.getEndDate()).toLowerCase() +
//				" " +	(iYear) +"г.)";
		v = "Отчетный период";
		C.setCellValue(v);
		setRowHeight(sheet, R, v, 13);
//		len = v.length();
//		height = 0; 
//		if(len > 0){
//		 int diff = len / 13;
//		 if(diff > 0){
////			 height = (diff + 1) * 300;
////			 R.setHeight((short)height);
//			 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//		 }
//		}		
		
		C.setCellValue(v);
		
		String Formula = "";
		
		row = 9;
		int value1 = 0;
		int value2 = 0;

		for(int i=0; i < rowCount; i++){
			value1 = 0;
			value2 = 0;

					MBCSACRInputFormAppend2Ext line = ListOfAppend2Ext.get(i);
					if(line != null){
						for(MBCSACRInputFormAppend2Ext l : ListOfAppend2Ext)
							if(line.getBCS_ACRPositionsDict_ID() == l.getBCS_ACRPositionsDict_ID()){
								value1 += l.getValue1();
								value2 += l.getValue2(); 
							}
					}
			
			R = sheet.getRow(row);
			C = R.createCell(column);
			if(line.isGroup()) 
				C.setCellStyle(bsv); 
			else 
				C.setCellStyle(sv);
			C.setCellValue(value1);
			
			C = R.createCell(column+1);
			if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
			C.setCellValue(value2);
			
			row++;
		}
		
		
//		appendix3------------------------------------------
		sheet = wb.getSheetAt(1);
		
		 row = 9;
		rowCount = 0;
		number = 1;
		column = 3;
		first = true;
		
		R = sheet.getRow(4);
		C = R.createCell(1);
		C.setCellStyle(sh);
		C.setCellValue("консолидированная");

		
		for(MBCSACRInputFormCompany company : companies){
			
			row = 9;
			number = 1;
			
			MBPartner p = new MBPartner(getCtx(), company.getC_BPartner_ID(), null);
			String name = "";
			if(p != null)
				name = p.getName();
				
			R = sheet.getRow(7);
			sheet.addMergedRegion(new Region((short)7, (short)column, (short)7, (short)(column+1)));
			C = R.createCell(column);
			C.setCellStyle(StyleHeader);
			C.setCellValue(name);
			C = R.createCell(column+1);
			C.setCellStyle(StyleHeader);

			
			R = sheet.getRow(8);
			sheet.setColumnWidth(column, 4000);
			C = R.createCell(column);
			C.setCellStyle(StyleHeader);
			
			
			Date d = mPeriodTo.getEndDate();
			Calendar c = new GregorianCalendar();
			c.setTime(d);
			c.add(Calendar.DAY_OF_MONTH, 1);
			
//			String month = "на 1 " + DataUtils.getMonthNameRU(c.getTime()).toLowerCase() + " " + c.get(Calendar.YEAR) + " г.";
//			
//			C.setCellValue(month);	//"на 1 января "+iYear+" года");
			C.setCellValue("на конец отчетного периода");
			C = R.createCell(column+1);
			C.setCellStyle(StyleHeader);
			sheet.setColumnWidth(column+1, 4000);
			
//			month = "на 1 " + DataUtils.getMonthNameRU(c.getTime()).toLowerCase() + " " + (c.get(Calendar.YEAR) -1 + " г.");
//			C.setCellValue(month);	//"на 1 января "+(iYear-1)+" года");
			C.setCellValue("на начало отчетного периода");
			for (MBCSACRInputFormAppend3_1Ext line : ListOfAppend3_1Ext) {


				if(company.getBCS_ACRInputForm_Company_ID() == line.getBCS_ACRInputForm_Company_ID()){
					if(first){
						rowCount++;
						
						R = sheet.createRow(row);
						if (R != null) {
							C = R.createCell(0);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
							if(line.getRow() != null)
								C.setCellValue(line.getRow());
							
							C = R.createCell(1);
							if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
							
							int len = line.getDictName().length();
							int height = 0; 
							if(len > 0){
							 int diff = len / 60;
							 if(diff > 0){
//								 height = (diff + 1) * 300;
//								 R.setHeight((short)height);
								 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));								 
							 }
							}
							
							C.setCellValue(line.getDictName());

							C = R.createCell(2);
							if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
							C.setCellValue("тыс. тенге");
							
							
							C = R.createCell(3);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
							C.setCellValue(""+line.getValue1());
							
							C = R.createCell(4);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
							C.setCellValue(""+line.getValue2());
	
						}
					}else{

							R = sheet.getRow(row);
								if (R != null) {
							
								C = R.createCell(column);
								if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
								C.setCellValue(""+line.getValue1());
								
								C = R.createCell(column+1);
								if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
								C.setCellValue(""+line.getValue2());
							}
					}
					row++;
				}
			}
			
			if(first){
				rowCount++;
				R = sheet.createRow(row);
				if(R != null){
					C = R.createCell(0);
					C.setCellStyle(bsv);
					C.setCellValue("");

					C = R.createCell(1);
					C.setCellStyle(boldStyle2);
					C.setCellValue("Показатели отчета о прибылях и убытках");
				
					C = R.createCell(2);
					C.setCellStyle(boldStyle2);
					C.setCellValue("Ед. измерения");
					
					setRowHeight(sheet, R, "Ед. измерения", 10);
//					len = "Ед. измерения".length();
//					height = 0; 
//					if(len > 0){
//					 int diff = len / 10;
//					 if(diff > 0){
////						 height = (diff + 1) * 300;
////						 R.setHeight((short)height);
//						 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));						 
//					 }
//					}
					
					C = R.createCell(3);
					C.setCellStyle(boldStyle);
					C.setCellValue("");

					C = R.createCell(4);
					C.setCellStyle(boldStyle);
					C.setCellValue("");

					
					
				}
			}else{
				R = sheet.getRow(row);
				if (R != null) {
			
					C = R.createCell(column);
					C.setCellStyle(bsv);
					C.setCellValue("");
					
					C = R.createCell(column+1);
					C.setCellStyle(bsv); 
					C.setCellValue("");
				}
			}
			
			row++;
			
			
			
			for (MBCSACRInputFormAppend3_2Ext line : ListOfAppend3_2Ext) {


				if(company.getBCS_ACRInputForm_Company_ID() == line.getBCS_ACRInputForm_Company_ID()){
					
					if(line.getRow() != null && line.getRow().equals("8")){
						
					if(first){
						rowCount++;
						R = sheet.createRow(row);
						if(R != null){
							C = R.createCell(0);
							C.setCellStyle(bsv);
							C.setCellValue("");

							C = R.createCell(1);
							C.setCellStyle(boldStyle2);
							C.setCellValue("Прочие уточняющие данные");
						
							C = R.createCell(2);
							C.setCellStyle(boldStyle2);
							C.setCellValue("Ед. измерения");
							
							setRowHeight(sheet, R, "Ед. измерения", 10);
//							int len = "Ед. измерения".length();
//							int height = 0; 
//							if(len > 0){
//							 int diff = len / 10;
//							 if(diff > 0){
////								 height = (diff + 1) * 300;
////								 R.setHeight((short)height);
//								 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));								 
//							 }
//							}
							
							C = R.createCell(3);
							C.setCellStyle(boldStyle);
							C.setCellValue("");

							C = R.createCell(4);
							C.setCellStyle(boldStyle);
							C.setCellValue("");

							
							
						}
					}else{
						R = sheet.getRow(row);
						if (R != null) {
					
							C = R.createCell(column);
							C.setCellStyle(bsv);
							C.setCellValue("");
							
							C = R.createCell(column+1);
							C.setCellStyle(bsv); 
							C.setCellValue("");
						}
					}
					
					row++;
				}
												
					if(first){
						
						rowCount++;
						R = sheet.createRow(row);

						if (R != null) {
							C = R.createCell(0);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(bsv);
							if(line.getRow() != null)
								C.setCellValue(line.getRow());
							setRowHeight(sheet, R, line.getDictName(), 60);
//							len = line.getDictName().length();
//							height = 0; 
//							if(len > 0){
//							 int diff = len / 60;
//							 if(diff > 0){
////								 height = (diff + 1) * 300;
////								 R.setHeight((short)height);
//								 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));								 
//							 }
//							}

							C = R.createCell(1);
							if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
							setRowHeight(sheet, R, line.getDictName(), 60);
//							len = line.getDictName().length();
//							height = 0; 
//							if(len > 0){
//							 int diff = len / 60;
//							 if(diff > 0){
////								 height = (diff + 1) * 300;
////								 R.setHeight((short)height);
//								 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));								 
//							 }
//							}
							
							C.setCellValue(line.getDictName());

							C = R.createCell(2);
							if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
							C.setCellValue("тыс. тенге");
							
							C = R.createCell(3);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
							C.setCellValue(""+line.getValue1());
							
							C = R.createCell(4);
							if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
							C.setCellValue(""+line.getValue2());
	
						}
					}else{

							R = sheet.getRow(row);
								if (R == null)
									R = sheet.createRow(row);
							
								C = R.createCell(column);
								if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
								C.setCellValue(""+line.getValue1());
								
								C = R.createCell(column+1);
								if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
								C.setCellValue(""+line.getValue2());
							
					}
					row++;
				}
			}
			
			column++;
			column++;
			first = false;
		}

		//summary-------------------------
		
		Date d = mPeriodTo.getEndDate();
		Calendar c = new GregorianCalendar();
		c.setTime(d);
		c.add(Calendar.DAY_OF_MONTH, 1);
		
		R = sheet.getRow(7);
		C = R.createCell(column+1);
		C.setCellStyle(StyleHeader);
		
		sheet.addMergedRegion(new Region((short)7, (short)column, (short)7, (short)(column+1)));
		C = R.createCell(column);
		C.setCellStyle(StyleHeader);
		C.setCellValue("Консолидированный");
		
		R = sheet.getRow(8);
		sheet.setColumnWidth(column, 4000);
		C = R.createCell(column);
		C.setCellStyle(StyleHeader);
//		String s = "на 1 "+DataUtils.getMonthNameRU(c.getTime()).toLowerCase()+" "+iYear+" г.";
		String s = "на конец отчетного периода";
		C.setCellValue(s);
//		C.setCellValue(s);

		C = R.createCell(column+1);
		C.setCellStyle(StyleHeader);
		sheet.setColumnWidth(column+1, 4000);			
//		String s2 = "на 1 "+DataUtils.getMonthNameRU(c.getTime()).toLowerCase()+" "+(iYear-1)+" г.";
//		C.setCellValue(s2);
		String s2 = "на начало отчетного периода";
		C.setCellValue(s2);
		
		
		int len = 0;
		if(s.length() > s2.length())
			setRowHeight(sheet, R, s, 12);
		else
			setRowHeight(sheet, R, s2, 12);

		
//		int height = 0; 
//		if(len > 0){
//		 int diff = len / 12;
//		 if(diff > 0){
////			 height = (diff + 1) * 300;
////			 R.setHeight((short)height);
//			 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));			 
//		 }
//		 
//		}

		row = 9;
		value1 = 0;
		value2 = 0;
		if(companies.size() > 1)
			rowCount = ListOfAppend3_1Ext.size() / companies.size();
		else
			rowCount = ListOfAppend3_1Ext.size();
		
		for(int i=0; i < rowCount; i++){
			value1 = 0;
			value2 = 0;
				
					MBCSACRInputFormAppend3_1Ext line = ListOfAppend3_1Ext.get(i);
					if(line != null){
						for(MBCSACRInputFormAppend3_1Ext l : ListOfAppend3_1Ext)
							if(line.getBCS_ACRPositionsDict_ID() == l.getBCS_ACRPositionsDict_ID()){
//								System.out.println(line.getBCS_ACRPositionsDict_ID() +":	"+l.getBCS_ACRPositionsDict_ID());
								value1 += l.getValue1();
								value2 += l.getValue2(); 
							}
					}
			
			R = sheet.getRow(row);
			if(R == null)
				R = sheet.createRow(row);
			
			C = R.createCell(column);
			if(line.isGroup()) 
				C.setCellStyle(bsv); 
			else 
				C.setCellStyle(sv);
			C.setCellValue(value1);
			
			C = R.createCell(column+1);
			if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
			C.setCellValue(value2);
			
			row++;
		}
		
//		row = 9;
		

		R = sheet.getRow(row);
		if(R == null)
			R = sheet.createRow(row);
		
		C = R.createCell(column);
		C.setCellStyle(bsv); 
		C = R.createCell(column+1);
		C.setCellStyle(bsv);		
		row++;
		
		value1 = 0;
		value2 = 0;

		if(companies.size() > 1)
			rowCount = ListOfAppend3_2Ext.size() / companies.size();
		else
			rowCount = ListOfAppend3_2Ext.size();
		
		for(int i=0; i < rowCount; i++){
			value1 = 0;
			value2 = 0;
				
					MBCSACRInputFormAppend3_2Ext line = ListOfAppend3_2Ext.get(i);
					if(line != null){
						for(MBCSACRInputFormAppend3_2Ext l : ListOfAppend3_2Ext)
							if(line.getBCS_ACRPositionsDict_ID() == l.getBCS_ACRPositionsDict_ID()){
//								System.out.println(line.getBCS_ACRPositionsDict_ID() +":	"+l.getBCS_ACRPositionsDict_ID());
								value1 += l.getValue1();
								value2 += l.getValue2(); 
							}
					}
			
			if(line.getRow() != null && line.getRow().equals("8")){
			
				R = sheet.getRow(row);
				C = R.createCell(column);
				C.setCellStyle(bsv);
				C.setCellValue("");
				R = sheet.getRow(row);
				C = R.createCell(column+1);
				C.setCellStyle(bsv);
				C.setCellValue("");
				row++;
			}		
			
				
				R = sheet.getRow(row);
				if(R == null)
					R = sheet.createRow(row);
				
				C = R.createCell(column);
				if(line.isGroup()) 
					C.setCellStyle(bsv); 
				else 
					C.setCellStyle(sv);
				C.setCellValue(value1);
				
				C = R.createCell(column+1);
				if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
				C.setCellValue(value2);
						
			row++;
		}		
		
		row++;
		
		R = sheet.createRow(row);
		R = sheet.getRow(row);
		C = R.createCell(0);
		C.setCellStyle(Style);
		sheet.addMergedRegion(new Region((short)row, (short)1, (short)row, (short)4));
		C = R.createCell(1);
		C.setCellStyle(StyleHeader);
		C.setCellValue("Сведения по предыдущему контролю/аудиту");
		C = R.createCell(2);
		C.setCellStyle(Style);
		C = R.createCell(3);
		C.setCellStyle(Style);
		C = R.createCell(4);
		C.setCellStyle(boldStyle);
		row++;

		number = 15;
		for (MBCSACRInputFormAppend3_3Ext line : ListOfAppend3_3Ext) {

			R = sheet.createRow(row);
			R = sheet.getRow(row);

			if (R != null) {
//				dict d = getDict(line.getBCS_ACRPositionsDict_ID());
				
				C = R.createCell(0);
				if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(bsv);
				C.setCellValue(number);
				
				sheet.addMergedRegion(new Region((short)row, (short)1, (short)row, (short)2));
				
				C = R.createCell(1);
				if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
				C.setCellValue(line.getDictName());
				setRowHeight(sheet, R, line.getDictName(), 70);
				
//				len = line.getDictName().length();
//				height = 0; 
//				if(len > 0){
//				 int diff = len / 70;
//				 if(diff > 0){
////					 height = (diff + 1) * 300;
////					 R.setHeight((short)height);
//					 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));					 
//				 }
//				 
//				}
				
				C = R.createCell(2);
				if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
				
				

				C = R.createCell(3);
				if(line.isGroup()) C.setCellStyle(bsv); else C.setCellStyle(sv);
				C.setCellValue(line.getValue1());
				
				C = R.createCell(4);
				C.setCellStyle(boldStyle);
				
								
//				C = R.createCell(4);
//				if(line.isGroup()) C.setCellStyle(boldStyle); else C.setCellStyle(Style);
//				C.setCellValue(line.getValue2());
			}
			row++;
			number++;
		}
		
		
		row++;
		row++;

		R = sheet.createRow(row);
		C = R.createCell(0);
		C = R.createCell(1);
		C.setCellStyle(remark);
		C.setCellValue("Примечание:");
		row++;
		R = sheet.createRow(row);
		C = R.createCell(0);
		C = R.createCell(1);
		C.setCellStyle(remark);
		C.setCellValue("1) приложение предоставляется как в бумажном, так и в электронном виде (Excel)");
		row++;
		R = sheet.createRow(row);
		C = R.createCell(0);
		C = R.createCell(1);
		C.setCellStyle(remark);
		C.setCellValue("2) все поля являются обязательными для заполнения");
		
		
		
		
		
		
		
		
		
		
		//---------------vgoo=============================================
		// vgoo2
		sheet = wb.getSheetAt(2);
		row = 5;
		int col = 3;
		number = 1;
		for (MBCSACRInputFormIntCompany line : interCompanyList) {

			R = sheet.getRow(row);
			if(R == null)
				R = sheet.createRow(row);
			
			C = R.createCell(col);
			C.setCellStyle(boldStyle);
			
			MBPartner bp = new MBPartner(Env.getCtx(), line.getC_BPartner_ID(), null);
			
			C.setCellValue(bp.getName());

			
			List<Integer> vgoo2 = getMBCSACRInputFormVGoo2Consolidation(bp.get_ID(), period.get_ID());
			int i=0;				

			//fill empties
			for(int j=0;j<5;j++){
				i++;
				R = sheet.getRow(row+i);
				if(R == null)
					R = sheet.createRow(row+i);
				
				C = R.createCell(col);
				C.setCellStyle(Style);
				C.setCellValue(0);

			}

			//fill datas
			i = 0;
			for(Integer vg: vgoo2){
				i++;
				R = sheet.getRow(row+i);
				if(R == null)
					R = sheet.createRow(row+i);
				
				C = R.createCell(col);
				C.setCellStyle(Style);
				C.setCellValue(vg);

			}

			
			col++;
		}


		
		// vgoo3
		sheet = wb.getSheetAt(3);
		row = 5;
		col = 3;
		number = 1;
		for (MBCSACRInputFormIntCompany line : interCompanyList) {

			R = sheet.getRow(row);
			if(R == null)
				R = sheet.createRow(row);
			
			C = R.createCell(col);
			C.setCellStyle(boldStyle);
			
			MBPartner bp = new MBPartner(Env.getCtx(), line.getC_BPartner_ID(), null);
			
			C.setCellValue(bp.getName());

			List<Integer> vgoo3 = getMBCSACRInputFormVGoo3Consolidation(bp.get_ID(), period.get_ID());
			int i=0;
			
			//fill empties
			for(int j=0;j<4;j++){
				i++;
				R = sheet.getRow(row+i);
				if(R == null)
					R = sheet.createRow(row+i);
				
				C = R.createCell(col);
				C.setCellStyle(Style);
				C.setCellValue(0);

			}

			//fill datas
			i = 0;
			for(Integer vg: vgoo3){
				i++;
				R = sheet.getRow(row+i);
				if(R == null)
					R = sheet.createRow(row+i);
				
				C = R.createCell(col);
				C.setCellStyle(Style);
				C.setCellValue(vg);

			}

			col++;
		}
		
		
		
		
		
		
		// ---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		
		if(!Env.isWindows())
			Util.launchFile(new File(file));
		else{
			try {
				String command = "excel \"" + file + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return Msg.translate(m_ctx, "Success");
	}
	
	protected void setRowHeight(HSSFSheet sheet, Row R, String source, int limitLength){
		int len = source.length();
		if(len > 0 && limitLength > 0){
		 int diff = len / limitLength;
		 if(diff > 0){
			 R.setHeightInPoints(((diff + 1) * sheet.getDefaultRowHeightInPoints()));								 
		 }
		}

	}

	protected MBCSACRInputFormPeriod getMBCSACRInputFormPeriod(BigDecimal _periodFrom, BigDecimal _periodTo,  BigDecimal year) {
		MBCSACRInputFormPeriod period = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_ACRInputForm_Period_ID FROM adempiere.BCS_ACRInputForm_Period WHERE PeriodFrom = '"
				+ _periodFrom + "' AND PeriodTo = '"+ _periodTo +"'  AND C_Year_ID = '" + year + "'";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				period = new MBCSACRInputFormPeriod(Env.getCtx(), rs.getInt(1),
						null);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return period;
	}
	
	protected List<MBCSACRInputFormCompany> getMBCSACRInputFormCompany(
			int MBCSACRInputFormPeriod_ID, BigDecimal c_bpartner) {
		List<MBCSACRInputFormCompany> company = new ArrayList<MBCSACRInputFormCompany>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_ACRInputForm_Company_ID FROM adempiere.BCS_ACRInputForm_Company \n WHERE BCS_ACRInputForm_Period_ID = "
				+ MBCSACRInputFormPeriod_ID; 
		if(c_bpartner != null && c_bpartner.compareTo(new BigDecimal(0)) == 1)
				sql += " and C_BPartner_ID = " + c_bpartner;

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				company.add(new MBCSACRInputFormCompany(Env.getCtx(),
						rs.getInt(1), null));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return company;
	}
	

	protected MBCSACRInputFormAppend1 getMBCSACRInputFormAppend1(
			int MBCSACRInputFormCompany_ID) {
		MBCSACRInputFormAppend1 append1 = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_ACRInputFormAppend1_ID FROM adempiere.BCS_ACRInputFormAppend1 WHERE BCS_ACRInputForm_Company_ID = "
				+ MBCSACRInputFormCompany_ID;

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				append1 = new MBCSACRInputFormAppend1(Env.getCtx(),
						rs.getInt(1), null);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return append1;
	}

	protected List<MBCSACRInputFormAppend2Ext> getMBCSACRInputFormAppend2Ext(List<MBCSACRInputFormCompany> companies ) {
		List<MBCSACRInputFormAppend2Ext> append2List = new ArrayList<MBCSACRInputFormAppend2Ext>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		
		for(MBCSACRInputFormCompany company: companies){
//			sql = "SELECT BCS_ACRInputFormAppend2_ID FROM adempiere.BCS_ACRInputFormAppend2 \n"
//					+ "	WHERE BCS_ACRInputForm_Company_ID = "
//					+ company.get_ID()
//					+ "\n	ORDER BY BCS_ACRInputFormAppend2_ID";
			sql = "SELECT append2.BCS_ACRInputFormAppend2_ID ,dict.issummary, dict.name	\n"
					+ "	FROM adempiere.BCS_ACRInputFormAppend2 append2	\n"
					+ "	inner join adempiere.bcs_acrpositionsdict dict on dict.bcs_acrpositionsdict_id = append2.bcs_acrpositionsdict_id	\n"
					+ "	WHERE append2.BCS_ACRInputForm_Company_ID = "
					+ company.get_ID()
					+ "\n	ORDER BY append2.BCS_ACRInputFormAppend2_ID";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					
					MBCSACRInputFormAppend2Ext append2 = new MBCSACRInputFormAppend2Ext(Env.getCtx(), rs
							.getInt(1), null);
					
					boolean isGroup = false;
					if(rs.getString(2) != null && rs.getString(2).equals("Y"))
						isGroup = true;
					
					append2.setGroup(isGroup);
					append2.setDictName(rs.getString(3));
					append2List.add(append2);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "BCS_ACReport", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}	
		return append2List;
	}

	protected List<MBCSACRInputFormAppend3_1Ext> getMBCSACRInputFormAppend3_1Ext(List<MBCSACRInputFormCompany> companies) {
		List<MBCSACRInputFormAppend3_1Ext> append3_1List = new ArrayList<MBCSACRInputFormAppend3_1Ext>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSACRInputFormCompany company: companies){
//			sql = "SELECT BCS_ACRInputFormAppend3_1_ID FROM adempiere.BCS_ACRInputFormAppend3_1 \n"
//					+ "	WHERE BCS_ACRInputForm_Company_ID = "
//					+ company.get_ID()
//					+ "\n	ORDER BY BCS_ACRInputFormAppend3_1_ID";
			sql = "SELECT append3_1.BCS_ACRInputFormAppend3_1_ID ,dict.issummary, dict.name, se.row, cu.name	\n"
					+ "	FROM adempiere.BCS_ACRInputFormAppend3_1 append3_1	\n"
					+ "	inner join adempiere.bcs_acrsetupinformappend3_1 se on se.bcs_acrpositionsdict_id = append3_1.bcs_acrpositionsdict_id	\n"
					+ " left join adempiere.c_uom_trl cu on cu.c_uom_id = se.c_uom_id	\n"
					+ "	inner join adempiere.bcs_acrpositionsdict dict on dict.bcs_acrpositionsdict_id = append3_1.bcs_acrpositionsdict_id	\n"
					+ "	WHERE append3_1.BCS_ACRInputForm_Company_ID = "
					+ company.get_ID()
					+ "\n	ORDER BY append3_1.BCS_ACRInputFormAppend3_1_ID";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					MBCSACRInputFormAppend3_1Ext append3_1 = new MBCSACRInputFormAppend3_1Ext(Env.getCtx(), rs
							.getInt(1), null);
					
					boolean isGroup = false;
					if(rs.getString(2) != null && rs.getString(2).equals("Y"))
						isGroup = true;
					
					append3_1.setGroup(isGroup);
					append3_1.setDictName(rs.getString(3));
					append3_1.setRow(rs.getString(4));
					append3_1.setUom(rs.getString(5));
					append3_1List.add(append3_1);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "BCS_ACReport", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return append3_1List;
	}

	protected List<MBCSACRInputFormAppend3_2Ext> getMBCSACRInputFormAppend3_2Ext(List<MBCSACRInputFormCompany> companies) {
		List<MBCSACRInputFormAppend3_2Ext> append3_2List = new ArrayList<MBCSACRInputFormAppend3_2Ext>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSACRInputFormCompany company: companies){
//			sql = "SELECT BCS_ACRInputFormAppend3_2_ID FROM adempiere.BCS_ACRInputFormAppend3_2 \n"
//					+ "	WHERE BCS_ACRInputForm_Company_ID = "
//					+ company.get_ID()
//					+ "\n	ORDER BY BCS_ACRInputFormAppend3_2_ID";
//			if(isConsolidation && company.getC_BPartner_ID() != 1000084)
//				continue;
			
			sql = "SELECT append3_2.BCS_ACRInputFormAppend3_2_ID ,dict.issummary, dict.name, se.row, cu.name	\n"
					+ "	FROM adempiere.BCS_ACRInputFormAppend3_2 append3_2	\n"
					+ " inner join adempiere.bcs_acrsetupinformappend3_2 se on se.bcs_acrpositionsdict_id = append3_2.bcs_acrpositionsdict_id	\n"
					+ "	left join adempiere.c_uom_trl cu on cu.c_uom_id = se.c_uom_id	\n"					
					+ "	inner join adempiere.bcs_acrpositionsdict dict on dict.bcs_acrpositionsdict_id = append3_2.bcs_acrpositionsdict_id	\n"
					+ "	WHERE append3_2.BCS_ACRInputForm_Company_ID = "
					+ company.get_ID()
					+ "\n	ORDER BY append3_2.BCS_ACRInputFormAppend3_2_ID";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					MBCSACRInputFormAppend3_2Ext append3_2 = new MBCSACRInputFormAppend3_2Ext(Env.getCtx(), rs
							.getInt(1), null);
					
					boolean isGroup = false;
					if(rs.getString(2) != null && rs.getString(2).equals("Y"))
						isGroup = true;
					
					append3_2.setGroup(isGroup);
					append3_2.setDictName(rs.getString(3));
					append3_2.setRow(rs.getString(4));
					append3_2.setUom(rs.getString(5));
					append3_2List.add(append3_2);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "BCS_ACReport", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return append3_2List;
	}

	protected List<MBCSACRInputFormAppend3_3Ext> getMBCSACRInputFormAppend3_3Ext(List<MBCSACRInputFormCompany> companies) {
		List<MBCSACRInputFormAppend3_3Ext> append3_3List = new ArrayList<MBCSACRInputFormAppend3_3Ext>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";
		for(MBCSACRInputFormCompany company: companies){
//			sql = "SELECT BCS_ACRInputFormAppend3_2_ID FROM adempiere.BCS_ACRInputFormAppend3_2 \n"
//					+ "	WHERE BCS_ACRInputForm_Company_ID = "
//					+ company.get_ID()
//					+ "\n	ORDER BY BCS_ACRInputFormAppend3_2_ID";
			if(isConsolidation && company.getC_BPartner_ID() != 1000084)
				continue;
			
			sql = "SELECT append3_3.BCS_ACRInputFormAppend3_3_ID ,dict.issummary, dict.name, se.row	\n"
					+ "	FROM adempiere.BCS_ACRInputFormAppend3_3 append3_3	\n"
					+ " inner join adempiere.bcs_acrsetupinformappend3_3 se on se.bcs_acrpositionsdict_id = append3_3.bcs_acrpositionsdict_id	\n"
					+ "	inner join adempiere.bcs_acrpositionsdict dict on dict.bcs_acrpositionsdict_id = append3_3.bcs_acrpositionsdict_id	\n"
					+ "	WHERE append3_3.BCS_ACRInputForm_Company_ID = "
					+ company.get_ID()
					+ "\n	ORDER BY append3_3.BCS_ACRInputFormAppend3_3_ID";
	
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					MBCSACRInputFormAppend3_3Ext append3_3 = new MBCSACRInputFormAppend3_3Ext(Env.getCtx(), rs
							.getInt(1), null);
					
					boolean isGroup = false;
					if(rs.getString(2) != null && rs.getString(2).equals("Y"))
						isGroup = true;
					
					append3_3.setGroup(isGroup);
					append3_3.setDictName(rs.getString(3));
					append3_3.setRow(rs.getString(4));
					append3_3List.add(append3_3);
				}
			} catch (SQLException e) {
				s_log.log(Level.SEVERE, "BCS_ACReport", e);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
		}
		return append3_3List;
	}
	
	
	protected List<MBCSACRInputFormAppend4> getMBCSACRInputFormAppend4(
			int MBCSACRInputFormCompany_ID) {
		List<MBCSACRInputFormAppend4> append4List = new ArrayList<MBCSACRInputFormAppend4>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_ACRInputFormAppend4_ID FROM adempiere.BCS_ACRInputFormAppend4 \n"
				+ "	WHERE BCS_ACRInputForm_Company_ID = "
				+ MBCSACRInputFormCompany_ID
				+ "\n	ORDER BY BCS_ACRInputFormAppend4_ID";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				append4List.add(new MBCSACRInputFormAppend4(Env.getCtx(), rs
						.getInt(1), null));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return append4List;
	}

	protected List<MBCSACRInputFormVGoo2> getMBCSACRInputFormVGoo2(
			int BCS_ACRInputForm_IntCompany_ID) {
		List<MBCSACRInputFormVGoo2> vgoo2List = new ArrayList<MBCSACRInputFormVGoo2>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_ACRInputFormVGOO2_ID FROM adempiere.BCS_ACRInputFormVGOO2 \n"
				+ "	WHERE BCS_ACRInputForm_IntCompany_ID = "
				+ BCS_ACRInputForm_IntCompany_ID
				+ "\n	ORDER BY BCS_ACRInputFormVGOO2_ID";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				vgoo2List.add(new MBCSACRInputFormVGoo2(Env.getCtx(), rs
						.getInt(1), null));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return vgoo2List;
	}

	protected List<MBCSACRInputFormVGoo3> getMBCSACRInputFormVGoo3(
			int BCS_ACRInputForm_IntCompany_ID) {
		List<MBCSACRInputFormVGoo3> vgoo3List = new ArrayList<MBCSACRInputFormVGoo3>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

		sql = "SELECT BCS_ACRInputFormVGOO3_ID FROM adempiere.BCS_ACRInputFormVGOO3 \n"
				+ "	WHERE BCS_ACRInputForm_IntCompany_ID = "
				+ BCS_ACRInputForm_IntCompany_ID
				+ "\n	ORDER BY BCS_ACRInputFormVGOO3_ID";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				vgoo3List.add(new MBCSACRInputFormVGoo3(Env.getCtx(), rs
						.getInt(1), null));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return vgoo3List;
	}

	protected List<Integer> getMBCSACRInputFormVGoo2Consolidation(
			int bpID, int periodID) {
		List<Integer> vgoo2List = new ArrayList<Integer>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

//		sql = "SELECT BCS_ACRInputFormVGOO2_ID FROM adempiere.BCS_ACRInputFormVGOO2 \n"
//				+ "	WHERE BCS_ACRInputForm_IntCompany_ID = "
//				+ BCS_ACRInputForm_IntCompany_ID
//				+ "\n	ORDER BY BCS_ACRInputFormVGOO2_ID";

		sql = "select \n"
			+ "		vg2.bcs_acrpositionsdict_ID,	\n"
			+ "	sum(vg2.value1) 	\n"
			+ "	from adempiere.BCS_ACRInputForm_Company c	\n"
			+ "	inner join adempiere.BCS_ACRInputForm_IntCompany ic on ic.BCS_ACRInputForm_Company_ID = c.BCS_ACRInputForm_Company_ID	\n"
			+ "	inner join adempiere.BCS_ACRInputFormVGOO2 vg2 on vg2.BCS_ACRInputForm_IntCompany_ID = ic.BCS_ACRInputForm_IntCompany_ID	\n"
			+ "	where c.BCS_ACRInputForm_Period_ID = "+	periodID	+"	\n"
			+ "	and c.C_BPartner_ID = "+	bpID	+"	\n"
			+ "	group by	\n"
			+ "	vg2.bcs_acrpositionsdict_ID	\n";
		
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				vgoo2List.add(rs.getInt(2));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return vgoo2List;
	}

	protected List<Integer> getMBCSACRInputFormVGoo3Consolidation(
			int bpID, int periodID) {
		List<Integer> vgoo3List = new ArrayList<Integer>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "";

//		sql = "SELECT BCS_ACRInputFormVGOO2_ID FROM adempiere.BCS_ACRInputFormVGOO2 \n"
//				+ "	WHERE BCS_ACRInputForm_IntCompany_ID = "
//				+ BCS_ACRInputForm_IntCompany_ID
//				+ "\n	ORDER BY BCS_ACRInputFormVGOO2_ID";

		sql = "select \n"
			+ "		vg3.bcs_acrpositionsdict_ID,	\n"
			+ "	sum(vg3.value1) 	\n"
			+ "	from adempiere.BCS_ACRInputForm_Company c	\n"
			+ "	inner join adempiere.BCS_ACRInputForm_IntCompany ic on ic.BCS_ACRInputForm_Company_ID = c.BCS_ACRInputForm_Company_ID	\n"
			+ "	inner join adempiere.BCS_ACRInputFormVGOO3 vg3 on vg3.BCS_ACRInputForm_IntCompany_ID = ic.BCS_ACRInputForm_IntCompany_ID	\n"
			+ "	where c.BCS_ACRInputForm_Period_ID = "+	periodID	+"	\n"
			+ "	and c.C_BPartner_ID = "+	bpID	+"	\n"
			+ "	group by	\n"
			+ "	vg3.bcs_acrpositionsdict_ID	\n";
		
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				vgoo3List.add(rs.getInt(2));
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		return vgoo3List;
	}
	
	
	public Object isNull(Object o) {
		return o == null ? new String() : o;
	}

}